package com.sansi.pencilbook.mapper;

import com.sansi.pencilbook.entity.Book;
import org.apache.ibatis.annotations.*;

import java.sql.SQLException;
import java.util.List;

/**
 * 功能说明:书籍的增删改查
 *
 * @author lsy13
 */
@Mapper
public interface BookMapper {

    @Select("select book_isbn,category_id,book_img,book_name,book_publishers," +
            "book_price,book_author,book_introduction,book_time from book")
    public List<Book> findAll() throws SQLException;

    @Select("select book_isbn,category_id,book_img,book_name,book_publishers," +
            "book_price,book_author,book_introduction,book_time from book where book_name like concat('%',#{name},'%')")
    public List<Book> findByName(String name) throws SQLException;

    @Select("select book_isbn,category_id,book_img,book_name,book_publishers," +
            "book_price,book_author,book_introduction,book_time from book where book_publishers like concat('%',#{publisher},'%')")
    public List<Book> findByPublisher(String publisher) throws SQLException;

    @Select("select book_isbn,category_id,book_img,book_name,book_publishers," +
            "book_price,book_author,book_introduction,book_time from book where book_author like concat('%',#{author},'%')")
    public List<Book> findByAuthor(String author) throws SQLException;

    @Select("select book_isbn,category_id,book_img,book_name,book_publishers," +
            "book_price,book_author,book_introduction,book_time from book where category_id = #{categoryId}")
    public List<Book> findByCategoryId(int categoryId) throws SQLException;

    @Select("select book_isbn,category_id,book_img,book_name,book_publishers," +
            "book_price,book_author,book_introduction,book_time from book where book_isbn = #{ISBN}")
    public Book findByISBN(String ISBN) throws SQLException;

    @Select("select book_isbn,category_id,book_img,book_name,book_publishers," +
        "book_price,book_author,book_introduction,book_time from book order by book_time desc")
    public List<Book> findByTime() throws SQLException;

    @Insert("insert into book (book_isbn,category_id,book_img,book_name,book_publishers," +
            "book_price,book_author,book_introduction,book_time) " +
            "values(#{bookIsbn},#{categoryId},#{bookImg},#{bookName},#{bookPublishers}," +
            "#{bookPrice},#{bookAuthor},#{bookIntroduction},#{bookTime})")
    public int saveBook(Book book) throws SQLException;

    @Insert("<script>" +
            "insert into book (book_isbn,category_id,book_img,book_name,book_publishers, " +
            "book_price,book_author,book_introduction,book_time) " +
            "values" +
            "<foreach collection='books' item='book' index='index' separator=','>" +
            "(#{book.bookIsbn},#{book.categoryId},#{book.bookImg},#{book.bookName},#{book.bookPublishers}," +
            "#{book.bookPrice},#{book.bookAuthor},#{book.bookIntroduction},#{book.bookTime})" +
            "</foreach>" +
            "</script>")
    public int saveBooks(@Param("books") List<Book> books) throws SQLException;

    @Update("update book set category_id=#{categoryId}," +
            "book_name=#{bookName},book_img=#{bookImg},book_publishers=#{bookPublishers}," +
            "book_price=#{bookPrice},book_author=#{bookAuthor}," +
            "book_introduction=#{bookIntroduction},book_time=#{bookTime} where book_isbn=#{bookIsbn}")
    public int updateBook(Book book) throws SQLException;

    @Update("<script>" +
        "<foreach collection='books' item='book' index='index' separator=','>" +
        "update book set category_id=#{categoryId}," +
        "book_name=#{bookName},book_img=#{bookImg},book_publishers=#{bookPublishers}," +
        "book_price=#{bookPrice},book_author=#{bookAuthor}," +
        "book_introduction=#{bookIntroduction},book_time=#{bookTime} where book_isbn=#{bookIsbn}" +
        "</foreach>" +
        "</script>")
    public int updateBooks(@Param("books") List<Book> books) throws SQLException;
}
